package com.yunhe.hibernate.test;


import com.yunhe.hibernate.entity.Clazz;
import com.yunhe.hibernate.entity.Degree;
import com.yunhe.hibernate.entity.Student;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;

import javax.persistence.criteria.CriteriaBuilder;
import java.math.BigDecimal;
import java.util.List;

public class ChaXun {

    public static void main(String[] args) {
        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
        Session session = sessionFactory.openSession();
//        List<Student> list = session.createQuery(" from Student ").list();

//        List<Student> list1 = session.createQuery("select new Student (s.name,s.clazz.name) from Student s ").list();

        //去重查询
//        List list2 = session.createQuery("select distinct c from Clazz c").list();

        //查询某个班级所有学生
//        List list3 = session.createQuery(" from Student s where s.clazz.name = '1班'").list();

        //查询java-01班女学生
//        List list4 = session.createQuery(" from Student s where s.clazz.name = '1班' and s.sex = '女'").list();

        //查询所有学生按照年龄排序
//        List<Student> list5 = session.createQuery(" from Student s order by s.height desc ").list();

        //查询年龄最大的两个学生
//        List<Student> list6 = session.createQuery(" from Student s order by s.birthday asc ").setFirstResult(0).setMaxResults(2).list();

        //查询某个学生最高的成绩
//        BigDecimal maxcj = session.createQuery("select max(d.score) from Degree d where d.student.id=1 ", BigDecimal.class).uniqueResult();

        //查询学生成绩
//        Degree o = session.createQuery("from Degree d where d.student.id=1 order by d.score desc ",Degree.class).setFirstResult(0).setMaxResults(1).uniqueResult();

        //1班有几个学生
       /* Query<Long> query = session.createQuery(" select count(s) from Student s where s.clazz.name = :clazzName ", Long.class);
        query.setParameter("clazzName","1班");
        Long aLong = query.uniqueResult();*/

       //统计各班级人数
//        List list8 = session.createQuery(" select c.name,size(c.studentList) from Clazz c group by c.name ").list();
//        List<Clazz> list9 = session.createQuery("select new Clazz(s.clazz.name,count(s)) from Student s group by s.clazz.id ").list();

        //统计1班 男女生人数并按照 人数倒序
//        List list = session.createQuery("select s.sex,count(s) as rs from Student s where s.clazz.name = '1班' group by s.sex order by rs desc").list();

        //查询成绩,但是只要 学生姓名,成绩,课程名
        List list10 = session.createQuery("select s.name as studentName,c.name as courseName,d.score from Student s left join  Degree d on s = d.student left join Course c on c = d.course ").list();
        session.close();
    }


}
